-- @Create_time :2023-08-28 10:00
-- @Author      :kumiler
-- @emial       :lukunming@jtexpress.com
-- @File        :execute_aggr.sql
-- @Desc        :中心应付派费网点汇总 只统计苏南代理区、苏北代理区、川藏代理区


insert overwrite table spmi_dm.dm_spmi_center_piece_fee_report_aggr_dt partition (dt)
select
null as id,
       piece_network_code,
       piece_network_name,
       piece_franchisee_code,
       piece_franchisee_name,
       piece_financial_center_code,
       piece_financial_center_name,
       sender_province_id,
       sender_province_name,
       sum(BF006) AS BF006,
       sum(BF012) AS BF012,
       sum(waybill_num) AS waybill_num,
       waybill_time,
       dt
from (
select waybill_no ,
       piece_network_code,
       piece_network_name,
       piece_franchisee_code,
       piece_franchisee_name,
       piece_financial_center_code,
       piece_financial_center_name,
       sender_province_id,
       sender_province_name,
       sum(BF006) AS BF006,
       sum(BF012) AS BF012,
       max(waybill_num) AS waybill_num,
       date_format(waybill_time,'yyyy-MM-dd') as waybill_time,
       date_format(sign_time,'yyyy-MM-dd') as dt
from spmi_dm.dm_spmi_center_piece_fee_report_detail_dt
where dt between date_add('{{ execution_date | cst_ds }}',-49) and '{{ execution_date | cst_ds }}'
       and piece_financial_center_code in (
                                     '320002',  -- 苏南代理区
                                     '320001',  -- 苏北代理区
                                     '510000'   -- 川藏代理区
         )
group by
waybill_no,
piece_network_code,
piece_network_name,
piece_franchisee_code,
piece_franchisee_name,
piece_financial_center_code,
piece_financial_center_name,
sender_province_id,
sender_province_name,
date_format(waybill_time,'yyyy-MM-dd'),
date_format(sign_time,'yyyy-MM-dd')
)  tt group by
       piece_network_code,
       piece_network_name,
       piece_franchisee_code,
       piece_franchisee_name,
       piece_financial_center_code,
       piece_financial_center_name,
       sender_province_id,
       sender_province_name,
       waybill_time,
       dt
distribute by dt
;

-- 当天更新的数据写入临时表，以便通过sqoop导入tidb中
insert overwrite table spmi_tmp.dm_spmi_center_piece_fee_report_aggr_dt
select null as id ,
       piece_network_code,
       piece_network_name,
       piece_franchisee_code,
       piece_franchisee_name,
       piece_financial_center_code,
       piece_financial_center_name,
       sender_province_id,
       sender_province_name,
       BF006 as BF009,
       BF012,
       waybill_num,
       dt,
       waybill_time
       from spmi_dm.dm_spmi_center_piece_fee_report_aggr_dt
where dt between date_add('{{ execution_date | cst_ds }}',-49)
and '{{ execution_date | cst_ds }}'
distribute by pmod(hash(rand()),22)
;